-- Create table
create table T_USERINFO
(
    ID         NUMBER not null,
    USER_NAME  VARCHAR2(16) not null,
    EXPERIENCE NUMBER not null,
    SEX        VARCHAR2(4) not null,
    SCORE      NUMBER not null,
    CITY       VARCHAR2(16) not null,
    SIGN       VARCHAR2(16) not null,
    CLASSIFY   VARCHAR2(24) not null,
    WORDS      NUMBER not null
)
    tablespace USERS
    pctfree 10
    initrans 1
    maxtrans 255
    storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-- Add comments to the columns
comment on column T_USERINFO.ID
    is 'id';
comment on column T_USERINFO.USER_NAME
    is '用户姓名';
comment on column T_USERINFO.EXPERIENCE
    is '积分';
comment on column T_USERINFO.SEX
    is '性别';
comment on column T_USERINFO.SCORE
    is '评分';
comment on column T_USERINFO.CITY
    is '城市';
comment on column T_USERINFO.SIGN
    is '签名';
comment on column T_USERINFO.CLASSIFY
    is '职业';
comment on column T_USERINFO.WORDS
    is '字数';

-- T_USERINFO添加序列
create sequence seq_userinfo_id;

select * from T_USERINFO;

insert into T_USERINFO (ID, USER_NAME, EXPERIENCE, SEX, SCORE, CITY, SIGN, CLASSIFY, WORDS)
values (seq_userinfo_id.nextval,'袁超11',266,'男',90,'南京','签名001','学者',900000)  ;

insert into T_USERINFO (ID, USER_NAME, EXPERIENCE, SEX, SCORE, CITY, SIGN, CLASSIFY, WORDS)
values (seq_userinfo_id.nextval,'袁超22',266,'男',90,'南京','签名001','学者',900000)  ;

insert into T_USERINFO (ID, USER_NAME, EXPERIENCE, SEX, SCORE, CITY, SIGN, CLASSIFY, WORDS)
values (seq_userinfo_id.nextval,'袁超33',266,'男',90,'南京','签名001','学者',900000)  ;

insert into T_USERINFO (ID, USER_NAME, EXPERIENCE, SEX, SCORE, CITY, SIGN, CLASSIFY, WORDS)
values (seq_userinfo_id.nextval,'袁超44',266,'男',90,'南京','签名001','学者',900000)  ;

insert into T_USERINFO (ID, USER_NAME, EXPERIENCE, SEX, SCORE, CITY, SIGN, CLASSIFY, WORDS)
values (seq_userinfo_id.nextval,'袁超55',266,'男',90,'南京','签名001','学者',900000)  ;
commit ;


--oracle分页
SELECT ROWNUM RN, t.* FROM (
    SELECT ID id , USER_NAME userName, EXPERIENCE experience,
       SEX sex,SCORE score,CITY city,SIGN sign ,CLASSIFY classify,WORDS words
    FROM T_USERINFO ) t WHERE ROWNUM <= 10 ;


SELECT * FROM (
                  (SELECT ROWNUM RN, t.* FROM
                      ( SELECT ID id , USER_NAME userName, EXPERIENCE experience,
                              SEX sex,SCORE score,CITY city,SIGN sign ,CLASSIFY classify,WORDS words
                       FROM T_USERINFO ) t WHERE ROWNUM <= 4 )
              ) WHERE  RN > 2  ;
--每页显示2条数数据
--页数(码)
-- 1        开始行数量(起始行) 0  结束行数量(结束行) 2
-- 2        开始行数量(起始行) 2  结束行数量(结束行) 4
-- 3        开始行数量(起始行) 4   结束行数量(结束行) 6
-- 4        开始行数量(起始行) 6  结束行数量(结束行) 8
-- 5        开始行数量(起始行) 8  结束行数量(结束行) 10

-- 分页起始行位置: ( page - 1 ) * 每页显示的数量
-- 分页结束行位置 : page * 每页显示的数量

--每页显示3条数数据
--页数(码)
-- 1        开始行数量(起始行) 0  结束行数量(结束行) 3
-- 2        开始行数量(起始行) 3  结束行数量(结束行) 6
-- 3        开始行数量(起始行) 6   结束行数量(结束行) 9
-- 4        开始行数量(起始行) 9  结束行数量(结束行) 12
-- 5        开始行数量(起始行) 12  结束行数量(结束行) 15
















